package com.niit.bookmanage.service.impl;

import com.niit.bookmanage.Utils.AbnormalUtils;
import com.niit.bookmanage.domain.vo.BorrowVO;
import com.niit.bookmanage.hive.PlatformDictionary;
import com.niit.bookmanage.service.SexService;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Liweijian.
 * @date 2024/6/7 18:45
 */

@Service
public class SexImpl implements SexService {

    /*
    * task 1
    * */
    @Override
    public int getCountBySex(String sex) throws SQLException {
        int count = 0;
        try {
            init();
            String sql =  "SELECT COUNT(*) AS count\n" +
                          "FROM library2\n" +
                          "WHERE user_sex = ? ";
            System.out.println("Running: " + sql);
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, sex);
            ResultSet rs = pstmt.executeQuery();  // 执行查询并获取结果集
            if (rs.next()) {
                count = rs.getInt("count");
            }
        } catch (SQLException e) {
            Object object = AbnormalUtils.getAbnormal(e);
            System.err.println(object);
        } finally {
            destroy();
        }
        //System.out.println(sex +":"+ count);
        return count;
    }

    @Override
    public List<BorrowVO> getSexCount() {
        int count1;
        int count2;
        try {
            count1 = getCountBySex("男");
            count2 = getCountBySex("女");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        ArrayList<BorrowVO> list = new ArrayList<>();
        BorrowVO vo = new BorrowVO("男", count1);
        BorrowVO vo1 = new BorrowVO("女", count2);
        list.add(vo);
        list.add(vo1);
        return list;
    }

    private static Connection conn = null;

    private static void init() {
        try {
            Class.forName(PlatformDictionary.DRIVER_NAME);
            conn = DriverManager.getConnection(PlatformDictionary.URL, PlatformDictionary.USER_NAME,
                    PlatformDictionary.PASSWORD);
        } catch (ClassNotFoundException | SQLException e) {
            Object object = AbnormalUtils.getAbnormal(e);
            System.err.println(object);
        }
    }

    /**
     * 释放资源
     */
    private static void destroy() {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            Object object = AbnormalUtils.getAbnormal(e);
            System.err.println(object);
        }
    }

}
